﻿using EmailManagementSoftware.Model;
using EmailManagementSoftware.Model.DAO.Interfaces;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace EmailManagementSoftware.Model.DAO
{
    public class AppDAO : IAppDAO
    {

        private string _connectionString = "Server = " +
                                      System.Configuration.ConfigurationManager.AppSettings["DB_HOST"] +
                                      "; Database = " +
                                      System.Configuration.ConfigurationManager.AppSettings["DB_NAME"];

        private SqlConnection _Connection { get; set; }
        private SqlCommand _Command { get; set; }
        private SqlDataReader _Reader { get; set; }


        private struct MultidimensionalKey
        {
            public readonly string Dimension1;
            public readonly SqlDbType Dimension2;

            public MultidimensionalKey(string d1, SqlDbType d2)
            {
                Dimension1 = d1;
                Dimension2 = d2;
            }
        }


        public User getUser(string username, string password)
        {
            User user = null;
            string SQLQuery = "SELECT * FROM [EmailManagementSoftware].[dbo].[Users] WHERE [username] LIKE @username AND [password] LIKE @password";

            Dictionary<MultidimensionalKey, object> SQLQueryParameters = new Dictionary<MultidimensionalKey, object>();
            SQLQueryParameters.Add(new MultidimensionalKey("@username", SqlDbType.NVarChar), username);
            SQLQueryParameters.Add(new MultidimensionalKey("@password", SqlDbType.NVarChar), password);


            using (_Connection = new SqlConnection(_connectionString))
            using (_Command = new SqlCommand(null, _Connection))
            using (_Reader)
            {
                try
                {
                    retrieveDatabaseResults(SQLQuery, SQLQueryParameters);


                    //process retrieved data in the Reader
                    if (_Reader.HasRows)
                    {
                        while (_Reader.Read())
                        {
                            user = new User();
                            user._idUser = _Reader.GetInt32(0);
                            user._username = _Reader.GetString(1);
                            user._password = _Reader.GetString(2);
                        }
                    }
                }
                catch (Exception)
                {
                    throw;
                }

            }
            return user;
        }


        public EmailCredentials getEmailCredentials(int idUser)
        {
            EmailCredentials emailCredentials = null;
            string SQLQuery = "SELECT * FROM [EmailManagementSoftware].[dbo].[EmailCredentials] WHERE [idUser] = @idUser";

            Dictionary<MultidimensionalKey, object> SQLQueryParameters = new Dictionary<MultidimensionalKey, object>();
            SQLQueryParameters.Add(new MultidimensionalKey("@idUser", SqlDbType.Int), idUser);


            using (_Connection = new SqlConnection(_connectionString))
            using (_Command = new SqlCommand(null, _Connection))
            using (_Reader)
            {
                try
                {
                    retrieveDatabaseResults(SQLQuery, SQLQueryParameters);


                    //process retrieved data in the Reader
                    if (_Reader.HasRows)
                    {
                        emailCredentials = new EmailCredentials();
                        while (_Reader.Read())
                        {
                            emailCredentials._idUser = _Reader.GetInt32(0);
                            emailCredentials._emailAddress = _Reader.GetString(1);
                            emailCredentials._password = _Reader.GetString(2);
                            emailCredentials._smtpServer = _Reader.GetString(3);
                            emailCredentials._smtpPort = _Reader.GetInt32(4);
                        }
                    }
                }
                catch (Exception)
                {
                    throw;
                }
            }
            return emailCredentials;
        }


        public IList<EmailMessage> getEmailMessages(int idUser)
        {
            List<EmailMessage> emailMessages = null;
            string SQLQuery = "SELECT * FROM [EmailManagementSoftware].[dbo].[EmailMessages] WHERE [idUser] = @idUser";

            Dictionary<MultidimensionalKey, object> SQLQueryParameters = new Dictionary<MultidimensionalKey, object>();
            SQLQueryParameters.Add(new MultidimensionalKey("@idUser", SqlDbType.Int), idUser);


            using (_Connection = new SqlConnection(_connectionString))
            using (_Command = new SqlCommand(null, _Connection))
            using (_Reader)
            {
                try
                {
                    retrieveDatabaseResults(SQLQuery, SQLQueryParameters);


                    //process retrieved data in the Reader
                    if (_Reader.HasRows)
                    {
                        emailMessages = new List<EmailMessage>();


                        DataTable dataTable = new DataTable();
                        dataTable.Load(_Reader);


                        foreach (DataRow dr in dataTable.Rows)
                        {
                            EmailMessage emailMessage = new EmailMessage();
                            emailMessage._idEmailMessage = (int)dr[0];
                            emailMessage._idUser = (int)dr[1];
                            emailMessage._emailSubject = dr[2].ToString();
                            emailMessage._recipient = dr[3].ToString();
                            emailMessage._emailBody = dr[4].ToString();

                            emailMessages.Add(emailMessage);
                        }
   
                    }
                }
                catch (Exception)
                {
                    throw;
                }
            }
            return emailMessages;
        }


        private void retrieveDatabaseResults(string SQLQuery, Dictionary<MultidimensionalKey, object> SQLQueryParameters)
        {
            try
            {
                _Connection.Open();

                //prepare the SQL query.
                _Command.CommandText = SQLQuery;

                //Iterating through Dictionary
                foreach (KeyValuePair<MultidimensionalKey, object> kvp in SQLQueryParameters)
                {
                    SqlParameter sqlParameter = new SqlParameter(kvp.Key.Dimension1, kvp.Key.Dimension2, Int32.MaxValue);
                    sqlParameter.Value = kvp.Value;
                    _Command.Parameters.Add(sqlParameter);
                }

                //Call Prepare after setting the Commandtext and Parameters.
                _Command.Prepare();
                _Reader = _Command.ExecuteReader();

            }
            catch (Exception)
            {
                throw;
            }
        }


        private void updateDatabase(String SQLCommand, Dictionary<MultidimensionalKey, object> SQLCommandParameters)
        {
            try
            {
                _Connection.Open();


                //prepare an SQL command.
                _Command.CommandText = SQLCommand;

                //Iterating through Dictionary
                foreach (KeyValuePair<MultidimensionalKey, object> kvp in SQLCommandParameters)
                {
                    SqlParameter sqlParameter = new SqlParameter(kvp.Key.Dimension1, kvp.Key.Dimension2, Int32.MaxValue);
                    sqlParameter.Value = kvp.Value;
                    _Command.Parameters.Add(sqlParameter);
                }

                //Call Prepare after setting the Commandtext and Parameters.
                _Command.Prepare();
                _Command.ExecuteNonQuery(); ;

            }
            catch (Exception)
            {
                throw;
            }
        }


        public void insertEmailCredentials(int idUser, string emailAddress, string password, string smtpServer, int port)
        {
            string SQLCommand = "INSERT INTO [EmailManagementSoftware].[dbo].[EmailCredentials]([idUser],[emailAddress],[password],[smtpServer],[port]) VALUES(@idUser,@emailAddress,@password,@smtpServer,@port)";

            Dictionary<MultidimensionalKey, object> SQLCommandParameters = new Dictionary<MultidimensionalKey, object>();
            SQLCommandParameters.Add(new MultidimensionalKey("@idUser", SqlDbType.Int), idUser);
            SQLCommandParameters.Add(new MultidimensionalKey("@emailAddress", SqlDbType.NVarChar), emailAddress);
            SQLCommandParameters.Add(new MultidimensionalKey("@password", SqlDbType.NVarChar), password);
            SQLCommandParameters.Add(new MultidimensionalKey("@smtpServer", SqlDbType.NVarChar), smtpServer);
            SQLCommandParameters.Add(new MultidimensionalKey("@port", SqlDbType.Int), port);


            using (_Connection = new SqlConnection(_connectionString))
            using (_Command = new SqlCommand(null, _Connection))
            using (_Reader)
            {
                try
                {
                    updateDatabase(SQLCommand, SQLCommandParameters);
                }
                catch (Exception)
                {
                    throw;
                }
            }
        }


        public void updateEmailCredentials(int idUser, string emailAddress, string password, string smtpServer, int port)
        {
            string SQLCommand = "UPDATE [EmailManagementSoftware].[dbo].[EmailCredentials] SET emailAddress=@emailAddress,password=@password,smtpServer=@smtpServer,port=@port  WHERE idUser=@idUser;";

            Dictionary<MultidimensionalKey, object> SQLCommandParameters = new Dictionary<MultidimensionalKey, object>();
            SQLCommandParameters.Add(new MultidimensionalKey("@emailAddress", SqlDbType.NVarChar), emailAddress);
            SQLCommandParameters.Add(new MultidimensionalKey("@password", SqlDbType.NVarChar), password);
            SQLCommandParameters.Add(new MultidimensionalKey("@smtpServer", SqlDbType.NVarChar), smtpServer);
            SQLCommandParameters.Add(new MultidimensionalKey("@port", SqlDbType.Int), port);
            SQLCommandParameters.Add(new MultidimensionalKey("@idUser", SqlDbType.Int), idUser);


            using (_Connection = new SqlConnection(_connectionString))
            using (_Command = new SqlCommand(null, _Connection))
            using (_Reader)
            {
                try
                {
                    updateDatabase(SQLCommand, SQLCommandParameters);
                }
                catch (Exception)
                {
                    throw;
                }
            }
        }


        public void insertEmailMessage(int idUser, string emailSubject, string recipient, string emailBody)
        {
            string SQLCommand = "INSERT INTO [EmailManagementSoftware].[dbo].[EmailMessages] ([idUser],[emailSubject],[recipient],[emailBody]) VALUES (@idUser,@emailSubject,@recipient,@emailBody)";

            Dictionary<MultidimensionalKey, object> SQLCommandParameters = new Dictionary<MultidimensionalKey, object>();
            SQLCommandParameters.Add(new MultidimensionalKey("@idUser", SqlDbType.Int), idUser);
            SQLCommandParameters.Add(new MultidimensionalKey("@emailSubject", SqlDbType.NVarChar), emailSubject);
            SQLCommandParameters.Add(new MultidimensionalKey("@recipient", SqlDbType.NVarChar), recipient);
            SQLCommandParameters.Add(new MultidimensionalKey("@emailBody", SqlDbType.NText), emailBody);


            using (_Connection = new SqlConnection(_connectionString))
            using (_Command = new SqlCommand(null, _Connection))
            using (_Reader)
            {
                try
                {
                    updateDatabase(SQLCommand, SQLCommandParameters);
                }
                catch (Exception)
                {
                    throw;
                }
            }
        }


        public void updateEmailMessage(string emailSubject, string recipient, string emailBody, int idEmailMessage)
        {
            string SQLCommand = "UPDATE [EmailManagementSoftware].[dbo].[EmailMessages] SET emailSubject=@emailSubject, recipient = @recipient, emailBody = @emailBody WHERE idEmailMessage=@idEmailMessage;";

            Dictionary<MultidimensionalKey, object> SQLCommandParameters = new Dictionary<MultidimensionalKey, object>();
            SQLCommandParameters.Add(new MultidimensionalKey("@emailSubject", SqlDbType.NVarChar), emailSubject);
            SQLCommandParameters.Add(new MultidimensionalKey("@recipient", SqlDbType.NVarChar), recipient);
            SQLCommandParameters.Add(new MultidimensionalKey("@emailBody", SqlDbType.NText), emailBody);
            SQLCommandParameters.Add(new MultidimensionalKey("@idEmailMessage", SqlDbType.Int), idEmailMessage);


            using (_Connection = new SqlConnection(_connectionString))
            using (_Command = new SqlCommand(null, _Connection))
            using (_Reader)
            {
                try
                {
                    updateDatabase(SQLCommand, SQLCommandParameters);
                }
                catch (Exception)
                {
                    throw;
                }
            }
        }


        public void deleteEmailMessage(int idEmailMessage)
        {
            string SQLCommand = "DELETE FROM [EmailManagementSoftware].[dbo].[EmailMessages] WHERE idEmailMessage=@idEmailMessage;";

            Dictionary<MultidimensionalKey, object> SQLCommandParameters = new Dictionary<MultidimensionalKey, object>();
            SQLCommandParameters.Add(new MultidimensionalKey("@idEmailMessage", SqlDbType.Int), idEmailMessage);


            using (_Connection = new SqlConnection(_connectionString))
            using (_Command = new SqlCommand(null, _Connection))
            using (_Reader)
            {
                try
                {
                    updateDatabase(SQLCommand, SQLCommandParameters);
                }
                catch (Exception)
                {
                    throw;
                }
            }
        }


        public IList<EmailAttachement> getEmailAttachements(int idEmailMessage)
        {
            IList<EmailAttachement> emailAttachements = null;
            string SQLQuery = "SELECT [idEmailAttachement] ,[attachementPath] ,[EmailManagementSoftware].[dbo].[EmailAttachements].[idEmailMessage] " +
                              "FROM [EmailManagementSoftware].[dbo].[EmailAttachements] INNER JOIN [EmailManagementSoftware].[dbo].[EmailMessages] " +
                              "ON [EmailManagementSoftware].[dbo].[EmailAttachements].[idEmailMessage] = [EmailManagementSoftware].[dbo].[EmailMessages].[idEmailMessage] " +
                              "WHERE [EmailManagementSoftware].[dbo].[EmailAttachements].[idEmailMessage] = @idEmailMessage";

            Dictionary<MultidimensionalKey, object> SQLQueryParameters = new Dictionary<MultidimensionalKey, object>();
            SQLQueryParameters.Add(new MultidimensionalKey("@idEmailMessage", SqlDbType.Int), idEmailMessage);


            using (_Connection = new SqlConnection(_connectionString))
            using (_Command = new SqlCommand(null, _Connection))
            using (_Reader)
            {
                try
                {
                    retrieveDatabaseResults(SQLQuery, SQLQueryParameters);


                    //process retrieved data in the Reader
                    if (_Reader.HasRows)
                    {
                        emailAttachements = new List<EmailAttachement>();
                        while (_Reader.Read())
                        {
                            EmailAttachement emailAttachement = new EmailAttachement();
                            emailAttachement._idEmailAttachement = _Reader.GetInt32(0);
                            emailAttachement._attachementPath = _Reader.GetString(1);
                            emailAttachement._idEmailMessage = _Reader.GetInt32(2);


                            emailAttachements.Add(emailAttachement);
                        }
                    }
                }
                catch (Exception)
                {
                    throw;
                }
            }
            return emailAttachements;
        }


        public void insertEmailAttachements(int idEmailMessage, IList<EmailAttachement> emailAttachements)
        {
            string SQLCommand = "INSERT INTO [EmailManagementSoftware].[dbo].[EmailAttachements] ([attachementPath],[idEmailMessage]) VALUES ";
            Dictionary<MultidimensionalKey, object> SQLCommandParameters = new Dictionary<MultidimensionalKey, object>();

            int count = emailAttachements.Count;
            for (var i = 1; i <= count; i++)
            {
                SQLCommand += "(@attachementPath"+i+", @idEmailMessage"+i+")";
                if (i != count)
                {
                    SQLCommand += ", ";
                }


                SQLCommandParameters.Add(new MultidimensionalKey("@attachementPath"+i, SqlDbType.NVarChar), emailAttachements[i-1]._attachementPath);
                SQLCommandParameters.Add(new MultidimensionalKey("@idEmailMessage" + i, SqlDbType.Int), emailAttachements[i-1]._idEmailMessage);
            }


            using (_Connection = new SqlConnection(_connectionString))
            using (_Command = new SqlCommand(null, _Connection))
            using (_Reader)
            {
                try
                {
                    updateDatabase(SQLCommand, SQLCommandParameters);
                }
                catch (Exception)
                {
                    throw;
                }
            }
        }


        public void deleteEmailAttachement(int idEmailAttachement)
        {
            string SQLCommand = "DELETE FROM [EmailManagementSoftware].[dbo].[EmailAttachements] WHERE idEmailAttachement=@idEmailAttachement;";

            Dictionary<MultidimensionalKey, object> SQLCommandParameters = new Dictionary<MultidimensionalKey, object>();
            SQLCommandParameters.Add(new MultidimensionalKey("@idEmailAttachement", SqlDbType.Int), idEmailAttachement);


            using (_Connection = new SqlConnection(_connectionString))
            using (_Command = new SqlCommand(null, _Connection))
            using (_Reader)
            {
                try
                {
                    updateDatabase(SQLCommand, SQLCommandParameters);
                }
                catch (Exception)
                {
                    throw;
                }
            }
        }


        public void deleteAllEmailAttachements(int idEmailMessage)
        {
            string SQLCommand = "DELETE FROM [EmailManagementSoftware].[dbo].[EmailAttachements] WHERE idEmailMessage=@idEmailMessage;";

            Dictionary<MultidimensionalKey, object> SQLCommandParameters = new Dictionary<MultidimensionalKey, object>();
            SQLCommandParameters.Add(new MultidimensionalKey("@idEmailMessage", SqlDbType.Int), idEmailMessage);


            using (_Connection = new SqlConnection(_connectionString))
            using (_Command = new SqlCommand(null, _Connection))
            using (_Reader)
            {
                try
                {
                    updateDatabase(SQLCommand, SQLCommandParameters);
                }
                catch (Exception)
                {
                    throw;
                }
            }
        }


        public EmailMessage getTheLastRecord()
        {
            EmailMessage emailMessage = null;
            string SQLQuery = "SELECT TOP 1 * FROM [EmailManagementSoftware].[dbo].[EmailMessages] ORDER BY [idEmailMessage] DESC";

            Dictionary<MultidimensionalKey, object> SQLQueryParameters = new Dictionary<MultidimensionalKey, object>();


            using (_Connection = new SqlConnection(_connectionString))
            using (_Command = new SqlCommand(null, _Connection))
            using (_Reader)
            {
                try
                {
                    retrieveDatabaseResults(SQLQuery, SQLQueryParameters);


                    //process retrieved data in the Reader
                    if (_Reader.HasRows)
                    {
                        emailMessage = new EmailMessage();
                        while (_Reader.Read())
                        {
                            emailMessage._idEmailMessage = _Reader.GetInt32(0);
                            emailMessage._idUser = _Reader.GetInt32(1);
                            emailMessage._emailSubject = _Reader.GetString(2);
                            emailMessage._recipient = _Reader.GetString(3);
                            emailMessage._emailBody = _Reader.GetString(4);
                        }
                    }
                }
                catch (Exception)
                {
                    throw;
                }
            }
            return emailMessage;
        }
    }
}